from . import db
from sqlalchemy.types import JSON
from uuid import uuid4
from sqlalchemy import func, DateTime


class Elderdemand(db.Model):
    __tablename__ = "elder_demand"
    id = db.Column(db.BigInteger, primary_key=True)
    elder = db.Column(db.String(255), nullable=False)
    actions = db.Column(db.String(255), nullable=False)
    arrange = db.Column(db.String(1), default="0")  # 新增字段，默认值为'0'

    def __repr__(self):
        return f"<Elder {self.id} {self.elder} arrange:{self.arrange}>"


class Attendantskills(db.Model):
    __tablename__ = "attendant_skills"
    id = db.Column(db.String(255), primary_key=True, default=lambda: str(uuid4()))
    attendant_name = db.Column(db.String(255), nullable=False)
    skills = db.Column(db.String(255), nullable=False)


class CaregiverSchedule(db.Model):
    __tablename__ = "caregiver_schedules"
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid4()))
    caregiver_id = db.Column(
        db.String(36), db.ForeignKey("attendant_skills.id"), nullable=False
    )
    schedule_date = db.Column(db.DateTime, nullable=False)
    time_slot = db.Column(db.Integer, nullable=False)
    is_available = db.Column(db.Boolean, default=True)

    def __repr__(self):
        return f"<CaregiverSchedule {self.caregiver_id} {self.schedule_date} {self.time_slot}>"


class DicNursingAction(db.Model):
    __tablename__ = "dic_nursing_actions"
    id = db.Column(db.BigInteger, primary_key=True)
    pid = db.Column(db.BigInteger)
    name = db.Column(db.String(255), nullable=False)
    level = db.Column(db.String(255))
    nursing_level = db.Column(db.String(255))

    def __repr__(self):
        return f"<DicNursingAction {self.id} {self.name}>"


class ScheduleResult(db.Model):
    __tablename__ = "care_schedule_results"
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid4()))
    elder = db.Column(db.String(36), nullable=False)
    schedule_date = db.Column(DateTime, nullable=False)
    schedule_data = db.Column(JSON, nullable=False)
    # 注意：数据库中不存在schedule_type和slots_count字段，请不要使用
    plan_date = db.Column(db.Date, nullable=True, index=True)
    batch_number = db.Column(db.Integer, nullable=True, index=True)
    create_time = db.Column(db.DateTime, nullable=True, default=func.now())
    update_time = db.Column(
        db.DateTime, nullable=True, default=func.now(), onupdate=func.now()
    )
    status = db.Column(db.String(20), nullable=True, default="active")

    def __repr__(self):
        return f"<ScheduleResult {self.id} {self.elder} {self.schedule_date}>"


class TimeSlot(db.Model):
    __tablename__ = "care_time_slots"
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid4()))
    schedule_result_id = db.Column(
        db.String(36), db.ForeignKey("care_schedule_results.id"), nullable=False
    )
    elder = db.Column(db.String(36), nullable=False)
    time_slot = db.Column(db.String(11), nullable=False)
    activity = db.Column(db.String(100), nullable=False)
    activity_id = db.Column(db.String(100), nullable=False)
    caregiver = db.Column(db.String(100), nullable=False)
    caregiver_id = db.Column(db.String(100), nullable=False)
    time_order = db.Column(db.Integer, nullable=False)
    schedule_date = db.Column(DateTime, nullable=False)
    # 新增字段
    plan_date = db.Column(db.Date, nullable=True, index=True)
    batch_number = db.Column(db.Integer, nullable=True, index=True)
    create_time = db.Column(db.DateTime, nullable=True, default=func.now())
    update_time = db.Column(
        db.DateTime, nullable=True, default=func.now(), onupdate=func.now()
    )
    status = db.Column(db.String(20), nullable=True, default="active")

    def __repr__(self):
        return f"<TimeSlot {self.id} {self.elder} {self.time_slot} {self.activity}>"

    @classmethod
    def get_ordered_slots(cls):
        return cls.query.order_by(
            cls.schedule_result_id,
            cls.elder,
            cls.schedule_date,
            func.substr(cls.time_slot, 1, 2),
            func.substr(cls.time_slot, 4, 2),
        ).all()


class ShiftAssignment(db.Model):
    __tablename__ = "shift_assignments"
    id = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid4()))
    caregiver_id = db.Column(
        db.String(255), db.ForeignKey("attendant_skills.id"), nullable=False
    )
    caregiver_name = db.Column(db.String(255), nullable=False)
    shift_date = db.Column(db.Date, nullable=False)
    shift_type = db.Column(
        db.String(20), nullable=False
    )  # 'morning', 'afternoon', 'night'
    shift_start_time = db.Column(db.Time, nullable=False)
    shift_end_time = db.Column(db.Time, nullable=False)
    schedule_type = db.Column(
        db.String(20), nullable=False, default="daily"
    )  # 'daily', 'weekly', 'monthly', 'quarterly', 'yearly'
    created_at = db.Column(db.DateTime, nullable=False, default=func.now())
    updated_at = db.Column(
        db.DateTime, nullable=False, default=func.now(), onupdate=func.now()
    )  # 更新时间字段
    plan_date = db.Column(db.Date, nullable=True, index=True)  # 排班计划日期
    batch_number = db.Column(db.Integer, nullable=True, index=True)  # 批次号
    status = db.Column(db.String(20), nullable=True, default="active")  # 状态

    def __repr__(self):
        return f"<ShiftAssignment {self.id} {self.caregiver_name} {self.shift_date} {self.shift_type}>"

    @classmethod
    def get_assignments_by_date_range(cls, start_date, end_date, date_desc=True):
        # 使用自定义排序逻辑，根据班次名称中的数字进行排序
        from sqlalchemy import case, desc

        # 定义班次排序规则
        shift_order = case(
            [
                (cls.shift_type == "班次1", 1),
                (cls.shift_type == "班次2", 2),
                (cls.shift_type == "班次3", 3),
            ],
            else_=4,
        )

        query = cls.query.filter(
            cls.shift_date >= start_date, cls.shift_date <= end_date
        )

        # 根据参数决定日期是升序还是降序
        if date_desc:
            return query.order_by(desc(cls.shift_date), shift_order).all()
        else:
            return query.order_by(cls.shift_date, shift_order).all()

    @classmethod
    def get_assignments_by_caregiver(
        cls, caregiver_id, start_date, end_date, date_desc=True
    ):
        # 使用自定义排序逻辑，根据班次名称中的数字进行排序
        from sqlalchemy import case, desc

        # 定义班次排序规则
        shift_order = case(
            [
                (cls.shift_type == "班次1", 1),
                (cls.shift_type == "班次2", 2),
                (cls.shift_type == "班次3", 3),
            ],
            else_=4,
        )

        query = cls.query.filter(
            cls.caregiver_id == caregiver_id,
            cls.shift_date >= start_date,
            cls.shift_date <= end_date,
        )

        # 根据参数决定日期是升序还是降序
        if date_desc:
            return query.order_by(desc(cls.shift_date), shift_order).all()
        else:
            return query.order_by(cls.shift_date, shift_order).all()
